package edu.cmu.aes.project2.wshelper;

/**
 This file: XMLProcessing.java
 Programmer: Prasanna Vaidyanathan (pvaidyan@andrew.cmu.edu)
 Course/Section: Architecting Enterprise Systems F11 A
 Assignment: Project 1
 Description: This file provides the capabilities to search models, view reviews, add review and sort 
 the data in XML file.
 Last Modified: 10/02/11
 */

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Scanner;

import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xquery.*;
import javax.xml.xquery.XQDataSource.*;

import javax.xml.parsers.*;
import javax.xml.xpath.*;

import net.sf.saxon.xqj.SaxonXQDataSource;

//import org.hsqldb.lib.Iterator;
import org.w3c.dom.*;
import org.xml.sax.SAXException;

/**
 * Implements functions for processing, manipulating and presenting XML data to
 * the user
 * 
 * @author Prasanna
 * 
 */
public class CopyOfXMLProcessing {

	private static String databaseLocation = "jdbc:mysql://localhost:3306/aes_project2";
	private static String userName = "root";
	private static String password = "root";
	private static String fileLocation = "D:/AllWorkSpace/workspace-aes/Supplier/Input/consolidated_data.xml";

	private static Connection getConnection() throws SQLException {
		Connection connection = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(databaseLocation,
					userName, password);
		} catch (Exception e) {
			throw new SQLException("Cannot instantiate database connector");
		}
		return connection;
	}

	/**
	 * @param args
	 * @throws XPathExpressionException
	 * @throws IOException
	 * @throws SAXException
	 * @throws ParserConfigurationException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws XQException
	 * @throws TransformerException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws XPathExpressionException,
			SAXException, IOException, ParserConfigurationException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, XQException, TransformerException,
			SQLException {
		// TODO Auto-generated method stub

		// ArrayList<String> result;
		CopyOfXMLProcessing xml = new CopyOfXMLProcessing();
		BikeInfo bikeinfo;
		bikeinfo = xml
				.getDetails("Bianchi MONO-Q Ultegra Compact 2010 Road Bike");
		System.out.println(bikeinfo);

		// // for(String i : result){
		// // System.out.println(i);
		// // }
		//
		Calendar cal = Calendar.getInstance();
		OrderItems[] orderitems = new OrderItems[2];
		orderitems[0] = new OrderItems("Bianchi", "Bianchi 2010", 5);
		orderitems[1] = new OrderItems("Pegasus", "Pegasus 2010", 10);
		java.sql.Date date = new java.sql.Date(cal.getTime().getTime());
		Order order = new Order(orderitems, "Prasanna", date);
		//
		// placeOrder(order);
		xml.placeOrder(order);

	}

	/**
	 * This function lists all the available brands and asks the user to choose
	 * a brand to continue
	 * 
	 * @param xqutil
	 *            contains the necessary class objects which will be used by the
	 *            XQuery API to manipulate data in XML
	 * @throws XQException
	 * @throws XPathExpressionException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 * @throws IOException
	 * @throws TransformerException
	 */
	public ArrayList<String> getBrands() {
		try {
			XQueryUtils xqutil = new XQueryUtils();

			ArrayList<String> brands = new ArrayList<String>();
			String es = "let $list := doc('"+fileLocation+"')/models/model/manufacturer "
					+ "for $n in  distinct-values($list) "
					+ "return <manufacturer>{$n}</manufacturer>";
			// execute the XQuery expression
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
			// process the result (sequence) iteratively

			Node node;
			NodeList nodeDetails = null;

			while (xqutil.getResult().next()) {
				node = xqutil.getResult().getNode();
				nodeDetails = node.getChildNodes();
				brands.add(nodeDetails.item(0).getNodeValue());

			}
			return brands;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * This function displays the List of available models in the brand chosen
	 * by the user
	 * 
	 * @param xqutil
	 * @param brand
	 * @throws XQException
	 * @throws XPathExpressionException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 * @throws IOException
	 * @throws TransformerException
	 */
	public ArrayList<String> getModels(String brand) {

		try {
			XQueryUtils xqutil = new XQueryUtils();
			ArrayList<String> models = new ArrayList<String>();

			String es = "for $n in fn:doc('"+fileLocation+"')/models/model "
					+ "where $n/manufacturer ='"
					+ brand
					+ "' "
					+ "return fn:data($n/@name)";
			// execute the XQuery expression
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
			// process the result (sequence) iteratively
			while (xqutil.getResult().next()) {
				// retrieve the current item of the sequence as a String
				String str = xqutil.getResult().getAtomicValue();
				models.add(str);
			}
			return models;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	/**
	 * This function prints all the details of the model such as price,
	 * manufacturer, raating etc
	 * 
	 * @param xqutil
	 * @param model
	 * @throws XQException
	 */
	public BikeInfo getDetails(String model) {
		try {
			XQueryUtils xqutil = new XQueryUtils();
			String es = "for $n in doc('"+fileLocation+"')/models/model "
					+ "where $n/@name ='" + model + "' " + "return $n/*";
			// execute the XQuery expression
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
			// process the result (sequence) iteratively

			BikeInfo bikeinfo = new BikeInfo();
			Node node;
			NodeList nodeDetails = null;
			while (xqutil.getResult().next()) {
				node = xqutil.getResult().getNode();

				nodeDetails = node.getChildNodes();
				int length = nodeDetails.getLength();
				String parentNodeName = node.getNodeName();
				String content = "";

				if (length > 1) {
					nodeDetails = node.getChildNodes();
					for (int i = 0; i < nodeDetails.getLength(); i++) {

						if (nodeDetails.item(i).getNodeName().equals("element")) {

							NamedNodeMap attributeslist = nodeDetails.item(i)
									.getAttributes();
							Attr attribute = (Attr) attributeslist.item(1);

							content += attribute.getNodeValue() + ":"
									+ nodeDetails.item(i).getTextContent()
									+ "\n";
						} else {
							content += nodeDetails.item(i).getNodeName() + ":"
									+ nodeDetails.item(i).getTextContent()
									+ "\n";
						}
					}
				} else {
					content += node.getNodeName() + ":" + node.getTextContent()
							+ "\n";
				}

//				if (parentNodeName.equalsIgnoreCase("manufacturer")) {
//					bikeinfo.setManufacturer(content);
//				} else if (parentNodeName.equalsIgnoreCase("price")) {
//					bikeinfo.setPrice(content);
//				} else if (parentNodeName.equalsIgnoreCase("frameset")) {
//					bikeinfo.setFrameset(content);
//				} else if (parentNodeName.equalsIgnoreCase("wheels")) {
//					bikeinfo.setWheels(content);
//				} else if (parentNodeName.equalsIgnoreCase("drivetrain")) {
//					bikeinfo.setDrivetrain(content);
//				} else if (parentNodeName.equalsIgnoreCase("rating")) {
//					bikeinfo.setRating(content);
//				} else if (parentNodeName.equalsIgnoreCase("components")) {
//					bikeinfo.setComponents(content);
//				}

			}
			return bikeinfo;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public boolean placeOrder(Order order){

		try {
			java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
			Connection conn = getConnection();
			OrderItems[] orderitems = order.getOrder();
			PreparedStatement pstmnt = conn
					.prepareStatement("insert into orders(customer_name,date) values (?,?)");
			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setDate(2, sqlDate);
			int result = pstmnt.executeUpdate();

			pstmnt = conn
					.prepareStatement("select * from orders order by order_id desc limit 1");
			ResultSet resultset = pstmnt.executeQuery();

			int orderid = 0;
			if (resultset.next())
				orderid = Integer.parseInt(resultset.getString("order_id"));

			for (int i = 0; i < orderitems.length; i++) {
				pstmnt = conn
						.prepareStatement("insert into orderitems(brand_name,model_name,quantity,order_id) values (?,?,?,?)");
				pstmnt.setString(1, orderitems[i].getBrandName());
				pstmnt.setString(2, orderitems[i].getModelName());
				pstmnt.setInt(3, orderitems[i].getQuantity());
				pstmnt.setInt(4, (orderid));
				result = pstmnt.executeUpdate();
				System.out.println(result);
			}
		} catch (Exception e) {
			System.out.println(e);
			return false;
		}

		return true;

	}

}
